package org.steven.crm.core.service;

import org.steven.crm.core.data.CascadeQueryConfig;
import org.steven.crm.core.data.ExtViewContext;
import org.steven.crm.core.data.TableRelation;
import org.steven.crm.core.data.pagination.PageSearchResult;
import org.steven.crm.core.exception.ServiceException;
import com.steven.common.utils.DateTimeUtil;
import com.steven.sequence.generater.SnowflakeGenerator;
import com.steven.xdb.data.FieldInfo;
import com.steven.xdb.datasource.ApplicationDataSource;
import com.steven.xdb.dbmanager.DBManager;
import com.steven.xdb.pagination.query.AbstractQueryDataList;
import com.steven.xdb.session.DBSession;
import com.steven.xdb.session.DBSessionHolder;
import org.apache.commons.lang3.StringUtils;

import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

public abstract class BaseService {
	
	private static ApplicationDataSource applicationDataSource;

	public static void setApplicationDataSource(ApplicationDataSource applicationDataSource) {
		BaseService.applicationDataSource = applicationDataSource;
	}

	protected DBSession getSession() throws SQLException {
		DBSession dbSession = DBSessionHolder.get();
		if (null == dbSession) {
			dbSession = applicationDataSource.getSession();
			dbSession.setAutoCommit(false);
			DBSessionHolder.set(dbSession);
		}

		return dbSession;
	}

	protected abstract String getTable();

	protected abstract AbstractQueryDataList getQueryDataList();

	protected List<CascadeQueryConfig> getCascadeQueryConfigs() {
		return null;
	};


	protected List<CascadeQueryConfig> filterCascadeQueryConfigsByGroup(List<CascadeQueryConfig> cascadeQueryConfigs) {
		String cascadeQueryGroup = ExtViewContext.getCascadeQueryGroup();
		if (StringUtils.isEmpty(cascadeQueryGroup)) {
			return cascadeQueryConfigs;
		}

		List<CascadeQueryConfig> result = new ArrayList<>();
		for (CascadeQueryConfig cascadeQueryConfig : cascadeQueryConfigs) {
			if (StringUtils.isEmpty(cascadeQueryConfig.getGroup())
				|| cascadeQueryGroup.equals(cascadeQueryConfig.getGroup())) {
				result.add(cascadeQueryConfig);
			}
		}

		return result;
	};


	protected abstract String getDataSourceName();

	public PageSearchResult<Map<String, String>> search(Map<String, String> condition, String extendSql,
                                                        List<String> extendValues, String sortSql,
                                                        int pageIndex, int pageNumber)  throws Exception {
		return search(getQueryDataList(), condition, extendSql, extendValues, sortSql, pageIndex, pageNumber);
	}

	public PageSearchResult<Map<String, Object>> searchExtView(Map<String, String> condition, String extendSql,
														List<String> extendValues, String sortSql,
														int pageIndex, int pageNumber)  throws Exception {
		PageSearchResult<Map<String, String>> pageSearchResult = search(getQueryDataList(), condition, extendSql,
				extendValues, sortSql, pageIndex, pageNumber);
		List<Map<String, String>> pageDatas = pageSearchResult.getDatas();
		List<Map<String, Object>> newPageDatas = new ArrayList<>();
		for (Map<String, String> pageData : pageDatas) {
			Map<String, Object> tmp = new HashMap<>();
			tmp.putAll(pageData);
			newPageDatas.add(tmp);
		}

		cascadeQuery(newPageDatas);

		PageSearchResult<Map<String, Object>> newPageSearchResult = new PageSearchResult<>();
		newPageSearchResult.setDatas(newPageDatas);
		newPageSearchResult.setPageCount(pageSearchResult.getPageCount());
		newPageSearchResult.setPageNumber(pageSearchResult.getPageNumber());
		newPageSearchResult.setPageIndex(pageSearchResult.getPageIndex());
		newPageSearchResult.setTotalCount(pageSearchResult.getTotalCount());

		return newPageSearchResult;
	}


    public PageSearchResult<Map<String, String>> search(AbstractQueryDataList queryDataListName,
                                      Map<String, String> condition, String extendSql,
                                      List<String> extendValues, String sortSql,
                                      int pageIndex, int pageNumber)  throws Exception {
		queryDataListName.setPageNumber(pageNumber);

		if (isSoftDeletedTable(getSession(), queryDataListName.getTableOrView())) {
			extendSql += " and deleted = ? ";
			extendValues.add("0");
		}
		if (StringUtils.isNotEmpty(sortSql)) {
			sortSql = " order by " + sortSql;
		}

		int count = getDataListCountByExtendSql(getSession(), queryDataListName, condition, extendSql, extendValues);
		List<Map<String, String>> datas = getDataListByExtendSql(getSession(), queryDataListName, pageIndex, pageNumber,
				condition, extendSql, extendValues, sortSql);

		int pageCount = count / pageNumber + (count % pageNumber > 0 ? 1 : 0);
		PageSearchResult result = new PageSearchResult();
		result.setDatas(datas);
		result.setTotalCount(count);
		result.setPageIndex(pageIndex);
		result.setPageNumber(pageNumber);
		result.setPageCount(pageCount);

		return result;
	}

	protected int getDataListCountByExtendSql(DBSession session, AbstractQueryDataList queryDataList,
										   Map<String, String> condition, String extendSql, List<String> extendValues) throws Exception {
    	List<String> paras = new ArrayList<>();
		String preWhereSql = queryDataList.makeWhereSql(condition, paras) + " " + extendSql;
		paras.addAll(extendValues);
		String sqlCount = "select count(*) as count from " + queryDataList.getTableOrView()
				+ preWhereSql.replaceFirst(" and ", " where ");

		List<Map<String, String>> datas = DBManager.query(session, sqlCount, paras);
		if (datas.size() > 0) {
			String count = datas.get(0).get("count");
			return Integer.parseInt(count);
		}

		return 0;
	}

	protected List<Map<String, String>> getDataListByExtendSql(DBSession session, AbstractQueryDataList queryDataList, int pageIndex, int pageNumber,
															Map<String, String> condition,
															String extendSql, List<String> extendValues, String sortSql) throws Exception {
		List<String> paras = new ArrayList<String>();
		String preWhereSql = queryDataList.makeWhereSql(condition, paras) + " " + extendSql + " " + sortSql;
		paras.addAll(extendValues);

		List<Map<String, String>> datas = DBManager.query(session,
				queryDataList.getTableOrView(), queryDataList.getColumns(), new HashMap<String, String>(),
				preWhereSql.replaceFirst(" and ", " where "), paras, "", (pageIndex - 1), pageNumber);

		return datas;
	}

	public Map<String, String> read(String id) throws Exception {
		if (StringUtils.isEmpty(id)) {
			throw new ServiceException("id为空");
		}
		Map<String, String> k = new HashMap<String, String>();
		k.put("id", id);
		if (isSoftDeletedTable(getSession(), getTable())) {
			k.put("deleted", "0");
		}
		return DBManager.queryOne(getSession(), getTable(), k);
	}

	public Map<String, Object> readExtView(String id) throws Exception {
		Map<String, String> data = this.read(id);
		Map<String, Object> newData = new HashMap<>();
		newData.putAll(data);

		cascadeQuery(newData);
		
		return newData;
	}

	public Map<String, String> readIngoreDeleted(String id) throws Exception {
		if (StringUtils.isEmpty(id)) {
			throw new ServiceException("id为空");
		}
		Map<String, String> k = new HashMap<String, String>();
		k.put("id", id);
		List<Map<String, String>> datas = DBManager.query(getSession(), getTable(), k);
		if (datas.size() > 0) {
			return datas.get(0);
		}
		return new HashMap<>();
	}

	public List<Map<String, String>> readByIds(Collection<String> ids) throws Exception {
		return readByIds(ids, "*");
	}

	public List<Map<String, String>> readByIds(Collection<String> ids, String columns) throws Exception {
		if (ids == null || ids.size() == 0) {
			return new ArrayList<>();
		}

		String sql = "select " + columns + " from " + getTable() + " where id in (" + String.join(",", ids) + ")";
		if (isSoftDeletedTable(getSession(), getTable())) {
			sql += " and deleted = 0";
		}

		return DBManager.query(getSession(), sql, new ArrayList<>());
	}

	public List<Map<String, String>> readByColumnValues(String conditionColumn, Collection<String> conditionValues,
														String resultColumns) throws Exception {
		if (conditionValues == null || conditionValues.size() == 0) {
			return new ArrayList<>();
		}

		if (!resultColumns.equals("*") && ("," + resultColumns + ",").indexOf("," + conditionColumn + ",") == -1) {
			resultColumns = resultColumns + "," + conditionColumn;
		}

		Collection<String> conditionValuesWithQuotation = conditionValues.stream().map(e -> "'" + e + "'").collect(Collectors.toList());
		String sql = "select " + resultColumns + " from " + getTable() + " where "
				+ conditionColumn + " in (" + String.join(",", conditionValuesWithQuotation) + ")";
		if (isSoftDeletedTable(getSession(), getTable())) {
			sql += " and deleted = 0";
		}

		return DBManager.query(getSession(), sql, new ArrayList<>());
	}

	public List<Map<String, String>> readByColumnValue(String conditionColumn, String conditionValue,
														String resultColumns) throws Exception {
		if (StringUtils.isEmpty(conditionValue)) {
			return new ArrayList<>();
		}

		if (!resultColumns.equals("*") && ("," + resultColumns + ",").indexOf("," + conditionColumn + ",") == -1) {
			resultColumns = resultColumns + "," + conditionColumn;
		}

		Map<String, String> k = new HashMap<>();
		k.put(conditionColumn, conditionValue);
		if (isSoftDeletedTable(getSession(), getTable())) {
			k.put("deleted", "0");
		}

		return DBManager.query(getSession(), getTable(), resultColumns, k);
	}

	public Map<String, String> readIncludeDeleted(String id) throws Exception {
		if (StringUtils.isEmpty(id)) {
			throw new ServiceException("id为空");
		}
		Map<String, String> k = new HashMap<String, String>();
		k.put("id", id);
		List<Map<String, String>> datas = DBManager.query(getSession(), getTable(), k);
		if (datas.size() > 0) {
			return datas.get(0);
		}
		return new HashMap<>();
	}


    public Map<String, String> save(Map<String, String> data) throws Exception {
		Map<String, String> result = new HashMap<>();
		if (StringUtils.isEmpty(data.get("id"))) {
			result = this.create(data);
		} else {
			result = this.update(data.get("id"), data);
		}
		return result;
	}

	public void beforeCreate(Map<String, String> data) throws Exception {}

	public Map<String, String> create(Map<String, String> data) throws Exception {
    	beforeCreate(data);
		DBSession session = getSession();
		String table = getTable();
		String id = StringUtils.isEmpty(data.get("id")) ? SnowflakeGenerator.getIndex() : data.get("id");
		data.put("id", id);
		data.put("addTime", DateTimeUtil.getCurrentDateTime());
		data.put("updateTime", DateTimeUtil.getCurrentDateTime());
		if (isSoftDeletedTable(session, table)) {
			data.put("deleted", "0");
		}
		DBManager.insert(session, table, data);

		Map<String, String> k = new HashMap<>();
		k.put("id", id);
		Map<String, String> result = DBManager.queryOne(session, table, k);
		afterCreate(result);
		moreAfterCreate(data, result);

		return result;
	}

	public void afterCreate(Map<String, String> data) throws Exception {}

	public void moreAfterCreate(Map<String, String> data, Map<String, String> createdData) throws Exception {}

	public void beforeUpdate(String id, Map<String, String> data) throws Exception {}

    public Map<String, String> update(String id, Map<String, String> dataV) throws Exception {
		beforeUpdate(id, dataV);
    	if (StringUtils.isEmpty(id)) {
    		throw new ServiceException("id为空");
		}
		Map<String, String> dataK = new HashMap<>();
		dataK.put("id", id);
		dataV.put("updateTime", DateTimeUtil.getCurrentDateTime());
		dataV.remove("id");
		DBManager.update(getSession(), getTable(), dataK, dataV);

		Map<String, String> k = new HashMap<>();
		k.put("id", id);
		Map<String, String> result =  DBManager.query(getSession(), getTable(), k).get(0);
		afterUpdate(result);

		return result;
	}

	public void afterUpdate(Map<String, String> data) throws Exception {}

	public void beforeDelete(String id) throws Exception {}

    public void delete(String id) throws Exception {
		beforeDelete(id);

		if (StringUtils.isEmpty(id)) {
			throw new ServiceException("id为空");
		}
		boolean isSoftDelete = isSoftDeletedTable(getSession(), getTable());

		Map<String, String> k = new HashMap<>();
		k.put("id", id);
		if (isSoftDelete) {
			Map<String, String> v = new HashMap<>();
			v.put("updateTime", DateTimeUtil.getCurrentDateTime());
			v.put("deleted", System.currentTimeMillis() + "");
			DBManager.update(getSession(), getTable(), k, v);
		}
		else {
			DBManager.delete(getSession(), getTable(), k);
		}

		afterDelete(id);
	}

	public void afterDelete(String id) throws Exception {}

	public boolean isUnique(Map<String, String> key) throws Exception {
		Map<String, String> checkData = new HashMap<>();
		checkData.putAll(key);

		String id = checkData.remove("id");
		if (checkData.isEmpty()) {
			return true;
		}

		if (isSoftDeletedTable(getSession(), getTable())) {
			checkData.put("deleted", "0");
		}
		if (StringUtils.isEmpty(id)) {
			return DBManager.query(getSession(), getTable(), checkData).size() == 0;
		} else {
			String extendSQL = " and id != ?";
			List<String> extendParams = new ArrayList<>();
			extendParams.add(id);
			return DBManager.query(getSession(), getTable(), "id", checkData, extendSQL, extendParams,
					"", 0, 1).size() == 0;
		}
	}

	protected boolean isSoftDeletedTable(DBSession session, String tableName) throws Exception {
		FieldInfo[] fields = session.getEntityInfo(tableName).getFieldInfo();
		for (FieldInfo fieldInfo : fields) {
			if (fieldInfo.getName().equalsIgnoreCase("deleted")) {
				return true;
			}
		}

		return false;
	}

	protected void cascadeQuery(List<Map<String, Object>> datas) throws Exception {
		List<CascadeQueryConfig> cascadeQueryConfigs = getCascadeQueryConfigs();
		if (cascadeQueryConfigs != null) {
			List<CascadeQueryConfig> finalCascadeQueryConfigs = filterCascadeQueryConfigsByGroup(cascadeQueryConfigs);
			for (CascadeQueryConfig cascadeQueryConfig : finalCascadeQueryConfigs) {
				cascadeQuery(datas, cascadeQueryConfig);
			}
		}
	}
	private void cascadeQuery(List<Map<String, Object>> datas, CascadeQueryConfig cascadeQueryConfig) throws Exception {
		List<String> srcTableColumnValues = new ArrayList<>();
		for (Map<String, Object> data : datas) {
			String srcTableColumnValue = (String) data.get(cascadeQueryConfig.getSrcColumn());
			if (StringUtils.isEmpty(srcTableColumnValue) || srcTableColumnValues.contains(srcTableColumnValue)) {
				continue;
			}
			srcTableColumnValues.add(srcTableColumnValue);
		}


		if (cascadeQueryConfig.getTableRelation() == TableRelation.MANY2MANY) {
			String middleTableResultColumns = cascadeQueryConfig.getMiddleTableSrcColumn() + "," + cascadeQueryConfig.getMiddleTableTargetColumn();
			List<Map<String, String>> middleTableDatas =
					cascadeQueryConfig.getMiddleService().readByColumnValues(cascadeQueryConfig.getMiddleTableSrcColumn(),
							srcTableColumnValues, middleTableResultColumns);
			Map<String, List<String>> srcTableRelationTargetTableDatas = new HashMap<>();
			List<String> targetTableValues = new ArrayList<>();
			for (Map<String, String> middleTableData : middleTableDatas) {
				String srcTableValue = middleTableData.get(cascadeQueryConfig.getMiddleTableSrcColumn());
				String targetTableValue = middleTableData.get(cascadeQueryConfig.getMiddleTableTargetColumn());

				if (!srcTableRelationTargetTableDatas.containsKey(srcTableValue)) {
					srcTableRelationTargetTableDatas.put(srcTableValue, new ArrayList<>());
				}
				srcTableRelationTargetTableDatas.get(srcTableValue).add(targetTableValue);

				if (!targetTableValues.contains(targetTableValue)) {
					targetTableValues.add(targetTableValue);
				}
			}

			List<Map<String, String>> targetTableDatas =
					cascadeQueryConfig.getTargetService().readByColumnValues(cascadeQueryConfig.getTargetColumn(),
							targetTableValues, String.join(",", Arrays.asList(cascadeQueryConfig.getTargetTableFields())));

			Map<String, Map<String, String>> targetTableDataMap = new HashMap<>();
			for (Map<String, String> targetTableData : targetTableDatas) {
				String keyValue = targetTableData.get(cascadeQueryConfig.getTargetColumn());
				targetTableDataMap.put(keyValue, targetTableData);
			}

			for (Map<String, Object> data : datas) {
				List<String> dataRelationTargetTableColumnValues = srcTableRelationTargetTableDatas.get(data.get(cascadeQueryConfig.getSrcColumn()));
				if (dataRelationTargetTableColumnValues == null) {
					continue;
				}

				List<Map<String, String>> dataRelationTargetTableDatas = new ArrayList<>();
				for (String dataRelationTargetTableColumnValue : dataRelationTargetTableColumnValues) {
					if (targetTableDataMap.containsKey(dataRelationTargetTableColumnValue)) {
						dataRelationTargetTableDatas.add(targetTableDataMap.get(dataRelationTargetTableColumnValue));
					}
				}

				data.put(cascadeQueryConfig.getValue(), dataRelationTargetTableDatas);
			}
		} else {
			List<Map<String, String>> targetTableDatas =
					cascadeQueryConfig.getTargetService().readByColumnValues(cascadeQueryConfig.getTargetColumn(),
							srcTableColumnValues, String.join(",", Arrays.asList(cascadeQueryConfig.getTargetTableFields())));

			Map<String, List<Map<String, String>>> targetTableDataMap = new HashMap<>();
			for (Map<String, String> targetTableData : targetTableDatas) {
				String keyValue = targetTableData.get(cascadeQueryConfig.getTargetColumn());
				if (!targetTableDataMap.containsKey(keyValue)) {
					targetTableDataMap.put(keyValue, new ArrayList<>());
				}
				targetTableDataMap.get(keyValue).add(targetTableData);
			}

			for (Map<String, Object> data : datas) {
				List<Map<String, String>> relationTableDatas = targetTableDataMap.get(data.get(cascadeQueryConfig.getSrcColumn()));
				if (relationTableDatas == null) {
					continue;
				}

				if (relationTableDatas.size() > 0 && cascadeQueryConfig.getTableRelation() == TableRelation.ONE2ONE) {
					data.put(cascadeQueryConfig.getValue(), relationTableDatas.get(0));
				} else if (cascadeQueryConfig.getTableRelation() == TableRelation.ONE2MANY
						|| cascadeQueryConfig.getTableRelation() == TableRelation.MANY2MANY) {
					data.put(cascadeQueryConfig.getValue(), relationTableDatas);
				}
			}
		}
	}

	protected void cascadeQuery(Map<String, Object> data) throws Exception {
		List<CascadeQueryConfig> cascadeQueryConfigs = getCascadeQueryConfigs();
		if (cascadeQueryConfigs != null) {
			List<CascadeQueryConfig> finalCascadeQueryConfigs = filterCascadeQueryConfigsByGroup(cascadeQueryConfigs);
			for (CascadeQueryConfig cascadeQueryConfig : finalCascadeQueryConfigs) {
				cascadeQuery(data, cascadeQueryConfig);
			}
		}
	}

	private void cascadeQuery(Map<String, Object> data, CascadeQueryConfig cascadeQueryConfig) throws Exception {
		if (cascadeQueryConfig.getTableRelation() == TableRelation.MANY2MANY) {
			String middleTableResultColumns = cascadeQueryConfig.getMiddleTableSrcColumn() + "," + cascadeQueryConfig.getMiddleTableTargetColumn();
			List<Map<String, String>> middleTableDatas =
					cascadeQueryConfig.getMiddleService().readByColumnValue(cascadeQueryConfig.getMiddleTableSrcColumn(),
							(String) data.get(cascadeQueryConfig.getSrcColumn()), middleTableResultColumns);

			Map<String, List<String>> srcTableRelationTargetTableDatas = new HashMap<>();
			List<String> targetTableValues = new ArrayList<>();
			for (Map<String, String> middleTableData : middleTableDatas) {
				String srcTableValue = middleTableData.get(cascadeQueryConfig.getMiddleTableSrcColumn());
				String targetTableValue = middleTableData.get(cascadeQueryConfig.getMiddleTableTargetColumn());

				if (!srcTableRelationTargetTableDatas.containsKey(srcTableValue)) {
					srcTableRelationTargetTableDatas.put(srcTableValue, new ArrayList<>());
				}
				srcTableRelationTargetTableDatas.get(srcTableValue).add(targetTableValue);

				if (!targetTableValues.contains(targetTableValue)) {
					targetTableValues.add(targetTableValue);
				}
			}

			List<Map<String, String>> targetTableDatas =
					cascadeQueryConfig.getTargetService().readByColumnValues(cascadeQueryConfig.getTargetColumn(),
							targetTableValues, String.join(",", Arrays.asList(cascadeQueryConfig.getTargetTableFields())));

			Map<String, Map<String, String>> targetTableDataMap = new HashMap<>();
			for (Map<String, String> targetTableData : targetTableDatas) {
				String keyValue = targetTableData.get(cascadeQueryConfig.getTargetColumn());
				targetTableDataMap.put(keyValue, targetTableData);
			}


			List<String> dataRelationTargetTableColumnValues = srcTableRelationTargetTableDatas.get(data.get(cascadeQueryConfig.getSrcColumn()));
			if (dataRelationTargetTableColumnValues == null) {
				return;
			}

			List<Map<String, String>> dataRelationTargetTableDatas = new ArrayList<>();
			for (String dataRelationTargetTableColumnValue : dataRelationTargetTableColumnValues) {
				if (targetTableDataMap.containsKey(dataRelationTargetTableColumnValue)) {
					dataRelationTargetTableDatas.add(targetTableDataMap.get(dataRelationTargetTableColumnValue));
				}
			}

			data.put(cascadeQueryConfig.getValue(), dataRelationTargetTableDatas);
		} else {
			List<Map<String, String>> targetTableDatas =
					cascadeQueryConfig.getTargetService().readByColumnValue(cascadeQueryConfig.getTargetColumn(),
							(String) data.get(cascadeQueryConfig.getSrcColumn()),
							String.join(",", Arrays.asList(cascadeQueryConfig.getTargetTableFields())));

			Map<String, List<Map<String, String>>> targetTableDataMap = new HashMap<>();
			for (Map<String, String> targetTableData : targetTableDatas) {
				String keyValue = targetTableData.get(cascadeQueryConfig.getTargetColumn());
				if (!targetTableDataMap.containsKey(keyValue)) {
					targetTableDataMap.put(keyValue, new ArrayList<>());
				}
				targetTableDataMap.get(keyValue).add(targetTableData);
			}


			List<Map<String, String>> relationTableDatas = targetTableDataMap.get(data.get(cascadeQueryConfig.getSrcColumn()));
			if (relationTableDatas == null) {
				return;
			}

			if (relationTableDatas.size() > 0 && cascadeQueryConfig.getTableRelation() == TableRelation.ONE2ONE) {
				data.put(cascadeQueryConfig.getValue(), relationTableDatas.get(0));
			} else if (cascadeQueryConfig.getTableRelation() == TableRelation.ONE2MANY) {
				data.put(cascadeQueryConfig.getValue(), relationTableDatas);
			}
		}
	}

}
